﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_GetJump]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_GetJump];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_GetJump]
    @FlowID int,
    @StepID int,
    @Priority int = 0
AS
BEGIN
SET NOCOUNT ON;

IF @Priority > 0
BEGIN

    SELECT
            a.*,
            c.field_description,
            coalesce(d.step_name, N'结束文档') as step_name
        FROM 
            dbo.uds_flow_jump a,
            dbo.uds_flow b,
            dbo.uds_flow_style_description c,
            dbo.uds_flow_step d
        WHERE
            a.flow_id= b.flow_id
            and b.style_id = c.style_id
            and c.field_name = a.fieldname
            and a.flow_id = d.flow_id
            and d.step_id = a.to_step_id
            and a.flow_id = @flowid
            and a.step_id = @stepid
            and a.Priority = @Priority;

END
ELSE
BEGIN
    DECLARE @Style_ID int;
    
    SET @Style_ID = 0;
    SELECT @Style_ID = style_id FROM dbo.uds_flow WHERE flow_id = @flowid;

    DECLARE @tempDescription TABLE (
        field_name nvarchar(100) COLLATE Chinese_PRC_CI_AS,
        field_description nvarchar(300) COLLATE Chinese_PRC_CI_AS
    );
    INSERT INTO @tempDescription SELECT field_name,field_description FROM dbo.uds_flow_style_description WHERE style_id = @style_id;
    INSERT INTO @tempDescription VALUES(N'caste', N'《职级》')

    DECLARE @tempStep TABLE (
        Flow_ID int,
        Step_ID int,
        step_name nvarchar(300) COLLATE Chinese_PRC_CI_AS
    );
    INSERT INTO @tempStep SELECT Flow_id,step_id,step_name FROM dbo.uds_flow_step WHERE flow_id = @flowid;    
    INSERT INTO @tempStep (Flow_id,step_id,step_name) VALUES(@flowid, 0, N'《文档结束》');
    
    SELECT
            a.*,
            c.field_description,
            coalesce(d.step_name, N'《结束文档》') as step_name
        FROM 
            dbo.uds_flow_jump a,
            @tempDescription c,
            @tempStep d
        WHERE
            c.field_name = a.fieldname
            and a.flow_id = d.flow_id
            and d.step_id = a.to_step_id
            and a.flow_id = @flowid
            and a.step_id = @stepid
        ORDER BY a.Priority    

END;

END
GO